﻿Imports System.Data.SqlClient
Public Class ComprasDAL
#Region "Incluir/Alterar"
    Public Function inseriralterar(com_codigo As Integer, com_dtchegada As Nullable(Of DateTime), com_dtemissao As Nullable(Of DateTime), com_dtpedido As DateTime, com_numnota As String, com_valorfrete As Decimal, com_valortotal As Decimal, for_codigo As Integer, usu_codigo As Integer, sta_codigo As Integer) As Integer
        Dim cmd = New SqlCommand
        Dim codigo As Integer
        Try
            cmd.CommandText = "execute SPCompras @com_codigo, @com_dtchegada, @com_dtemissao, @com_dtpedido, @com_numnota, @com_valorfrete, @com_valortotal, @sta_codigo, @for_codigo, @usu_codigo"
            cmd.Parameters.Add(New SqlParameter("@com_codigo", SqlDbType.Int)).Value = com_codigo
            If (com_dtchegada.HasValue) Then
                cmd.Parameters.Add(New SqlParameter("@com_dtchegada", SqlDbType.DateTime)).Value = com_dtchegada
            Else
                cmd.Parameters.Add(New SqlParameter("@com_dtchegada", SqlDbType.DateTime)).Value = DBNull.Value
            End If
            If (com_dtemissao.HasValue) Then
                cmd.Parameters.Add(New SqlParameter("@com_dtemissao", SqlDbType.DateTime)).Value = com_dtemissao
            Else
                cmd.Parameters.Add(New SqlParameter("@com_dtemissao", SqlDbType.DateTime)).Value = DBNull.Value
            End If
            cmd.Parameters.Add(New SqlParameter("@com_dtpedido", SqlDbType.DateTime)).Value = com_dtpedido
            cmd.Parameters.Add(New SqlParameter("@com_numnota", SqlDbType.VarChar)).Value = com_numnota
            cmd.Parameters.Add(New SqlParameter("@com_valorfrete", SqlDbType.Decimal)).Value = com_valorfrete
            cmd.Parameters.Add(New SqlParameter("@com_valortotal", SqlDbType.Decimal)).Value = com_valortotal
            cmd.Parameters.Add(New SqlParameter("@for_codigo", SqlDbType.Int)).Value = for_codigo
            cmd.Parameters.Add(New SqlParameter("@usu_codigo", SqlDbType.Int)).Value = usu_codigo
            cmd.Parameters.Add(New SqlParameter("@sta_codigo", SqlDbType.Int)).Value = sta_codigo
            codigo = objconexao.executacmdAI(cmd, "Ofi_Compras")
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return codigo
    End Function
#End Region

#Region "Excluir"
    Public Function excluir(com_codigo As Integer, usuario As Integer) As Integer
        Dim cmd = New SqlCommand
        Dim codigo As Integer
        Try
            cmd.CommandText = "delete from Estoque.Ofi_Compras where com_codigo=@com_codigo"
            cmd.Parameters.Add(New SqlParameter("@com_codigo", SqlDbType.Int, 1)).Value = com_codigo
            codigo = objconexao.executaCmd(cmd)
            objfuncoes.gravaocorrencias("Exclusão de compras", com_codigo, 15, usuario)
        Catch ex As Exception
            Throw ex

        Finally
            cmd = Nothing

        End Try
        Return codigo
    End Function
#End Region

#Region "Buscar"
    Function buscar(ByVal descricao As String, tipo As Integer, descricao2 As String) As DataTable
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            Select Case tipo
                Case 1
                    'por codigo
                    cmd.CommandText = "select * from VW_EstCompras where  com_codigo= @codigo"
                    cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Val(descricao)
                Case 2
                    'por status
                    cmd.CommandText = "select * from VW_EstCompras where  sta_codigo= @codigo "
                    cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Val(descricao)

                Case 3
                    'dt emissao

                    cmd.CommandText = "select * from VW_EstCompras where  com_dtemissao between @codigo and @codigo2"
                    descricao += " 00:00:00"
                    descricao2 += " 23:59:59"
                    cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.DateTime)).Value = FormatDateTime(descricao, DateFormat.GeneralDate)
                    cmd.Parameters.Add(New SqlParameter("@codigo2", SqlDbType.DateTime)).Value = FormatDateTime(descricao2, DateFormat.GeneralDate)
                Case 4
                    'dt pedido
                    cmd.CommandText = "select * from VW_EstCompras where  com_dtpedido between @codigo and @codigo2"
                    descricao += " 00:00:00"
                    descricao2 += " 23:59:59"
                    cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.DateTime)).Value = FormatDateTime(descricao, DateFormat.GeneralDate)
                    cmd.Parameters.Add(New SqlParameter("@codigo2", SqlDbType.DateTime)).Value = FormatDateTime(descricao2, DateFormat.GeneralDate)
                Case 5
                    'dt chegada
                    cmd.CommandText = "select * from VW_EstCompras where  com_dtchegada between @codigo and @codigo2"
                    descricao += " 00:00:00"
                    descricao2 += " 23:59:59"
                    cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.DateTime)).Value = FormatDateTime(descricao, DateFormat.GeneralDate)
                    cmd.Parameters.Add(New SqlParameter("@codigo2", SqlDbType.DateTime)).Value = FormatDateTime(descricao2, DateFormat.GeneralDate)
                Case 6
                    'fornecedor
                    cmd.CommandText = "select * from VW_EstCompras where  for_fantasia like @codigo"
                    cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.VarChar)).Value = "%" & descricao & "%"

                Case Else
                    'todos
                    cmd.CommandText = "select * from VW_EstCompras where sta_codigo=4"
            End Select
            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
#End Region
End Class
